package com.my.easypoi.test.simple;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.util.LinkedHashMap;

/*excel读取为json*/
@Slf4j
public class Excel2 {
//    private final static Logger logger = LoggerFactory.getLogger(ExcelTranslator.class);

    private final static ObjectMapper mapper = new ObjectMapper();
    /*读取excel的值为json*/
    public static void main(String[] args) {
        try {
            //最终返回的json对象
            JSONObject rowObj = new JSONObject(new LinkedHashMap());

            FileInputStream inp = new FileInputStream("C:\\Users\\ycwb0688\\Desktop\\hsjcnl.xlsx");
            Workbook workbook = WorkbookFactory.create(inp);
            Sheet sheettimo = workbook.getSheetAt(0);//要读取页
            int rownum11s= sheettimo.getPhysicalNumberOfRows();//总行数

            JSONArray jsonArray = new JSONArray();//array类型 子集存放位置

            for(int a = 2; a < rownum11s ; a++){
                Row row1 = sheettimo.getRow(a);//获取第a行
                Cell cellData = row1.getCell(0);
                Cell cellData1 = row1.getCell(1);
                Cell cellData2 = row1.getCell(2);
                Cell cellData3 = row1.getCell(3);
                Cell cellData4 = row1.getCell(4);
                Cell cellData5 = row1.getCell(5);
                Cell cellData6 = row1.getCell(6);
                Cell cellData7 = row1.getCell(7);
                Cell cellData8 = row1.getCell(8);
                Cell cellData9 = row1.getCell(9);
                JSONObject sunrowObj = new JSONObject(new LinkedHashMap());//array类型中的每一个json对象存放
                sunrowObj.put("address",cellData.getStringCellValue());
                sunrowObj.put("name",cellData1.getStringCellValue());
                sunrowObj.put("org_top",cellData2.getStringCellValue());
                sunrowObj.put("org_type",cellData3.getStringCellValue());
                sunrowObj.put("org_level",cellData4.getStringCellValue());
                sunrowObj.put("responsible",cellData5.getStringCellValue());
                sunrowObj.put("responsible_phone",cellData6.getStringCellValue());
                sunrowObj.put("jc_capacity_max_num",cellData7.getNumericCellValue());
                sunrowObj.put("jc_qx_capacity_num",cellData8.getNumericCellValue());
                sunrowObj.put("lan",cellData9.getStringCellValue().split(",")[0]);
                sunrowObj.put("lat",cellData9.getStringCellValue().split(",")[1]);
                jsonArray.add(sunrowObj);
            }
            log.info(jsonArray.toJSONString());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}